package contacts.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import contacts.db.DbConnection;
import contacts.po.Room;
import contacts.dao.IRoomDao;

public class RoomDaoImpl implements IRoomDao{
	/**
	 *
	 *新增宿舍
	 *
	 */
	@Override
	public void save(Room room) {
		Connection conn = DbConnection.getConnection();
		int roomNo = room.getRoomNo();
		String roomName = room.getRoomName();
		String sql = "INSERT INTO room VALUES(?,?)";
		PreparedStatement ps = null;
		try {
			ps  = conn.prepareStatement(sql);;
	        ps.setInt(1,roomNo);
			ps.setString(2,roomName);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbConnection.close(null, ps);
		}	

	}
	/**
	 *
	 *更新宿舍信息
	 *
	 */
	@Override
	public void update(Room room) {
		Connection conn = DbConnection.getConnection();
		int roomNo = room.getRoomNo();
		String roomName = room.getRoomName();
		String sql = "UPDATE room SET room_name=? WHERE room_no=?";       
	    PreparedStatement ps = null;
	try {
		ps = conn.prepareStatement(sql);
		ps.setString(1,roomName);
        ps.setInt(2,roomNo);
	    ps.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DbConnection.close(null, ps);
	}	
	}
	/**
	 *删除宿舍的信息
	 *
	 *
	 */
	@Override
	public void delete(int id) {
		Connection conn = DbConnection.getConnection();
		String sql = "DELETE FROM room WHERE room_no=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
		
	}

	/**
	 * 根据宿舍号查询宿舍的信息 room_no为主键
	 *
	 */
	@Override
	public Room get(int id) {
		Connection conn = DbConnection.getConnection();
		List<Room> roomList = new ArrayList<Room>();
		String sql = "SELECT room_no,room_name FROM room WHERE room_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()){
				int roomNo = rs.getInt(1);
				String roomName = rs.getString(2);
				Room room = new Room(roomNo,roomName);
			    roomList.add(room);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return roomList.get(0);
	}
	/**
	 *查询所有宿舍的信息
	 *
	 */
	@Override
	public List<Room> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Room> roomList = new ArrayList<Room>();
		String sql = "SELECT room_no,room_name FROM room";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()){
				int roomNo = rs.getInt(1);
				String roomName = rs.getString(2);
				Room room = new Room(roomNo,roomName);
			    roomList.add(room);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return roomList;
	}

}
